package com.zrx.govern.component.easyexcel.starter.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.zrx.govern.component.easyexcel.starter.config.ExcelConfigProperties;
import com.zrx.govern.component.easyexcel.starter.constants.ExcelCheckConstants;
import com.zrx.govern.component.easyexcel.starter.entity.ExcelField;
import com.zrx.govern.component.easyexcel.starter.entity.ExcelModel;
import com.zrx.govern.component.easyexcel.starter.entity.FieldDataType;
import com.zrx.govern.component.easyexcel.starter.enums.ExcelEnum;
import com.zrx.govern.component.easyexcel.starter.handler.CustomFormatCellWriteHandler;
import com.zrx.govern.component.easyexcel.starter.handler.CustomHeaderCellWriteHandler;
import com.zrx.govern.component.easyexcel.starter.handler.CustomRowWriteHandler;
import com.zrx.govern.component.easyexcel.starter.strategy.ExcelAutoColumnWidthStyleStrategy;
import com.zrx.govern.component.easyexcel.starter.strategy.HeadStyleStrategy;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author 言曌
 */
@Slf4j
public class ExcelUtil {


    /**
     * 支持注解格式@ExcelProperty的导出
     * 数字设置单元格格式 直接添加@ContentStyle(dataFormat = 2)即可，其它自行类比
     *
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    @SneakyThrows
    public static void downloadExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz, ExcelEnum excelEnum, CustomRowWriteHandler customRowWriteHandler) {
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();
        excelEnum = Objects.isNull(excelEnum) ? ExcelEnum.XLSX : excelEnum == ExcelEnum.XLSX ? ExcelEnum.XLSX : ExcelEnum.XLS;
        ExcelTypeEnum excelTypeEnum = ExcelEnum.getExcelTypeEnumByValue(excelEnum);
        sheetName = StringUtils.isBlank(sheetName) ? "sheet1" : sheetName;
        /*核验文件名*/
        fileName = checkAndRenameExcelFile(fileName, excelEnum);
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(getOutputStream(fileName, excelEnum, response), clazz);
        if (Objects.nonNull(customRowWriteHandler)) {
            excelWriterBuilder.registerWriteHandler(customRowWriteHandler);
        }
        excelWriterBuilder.excelType(excelTypeEnum).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
    }

    /**
     * 根据动态表头写入excel数据并下载
     * 传入CustomCellWriteHandler对象，表示当前有进度计算
     *
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param headList
     * @param excelEnum
     * @param customRowWriteHandler 进度控制器
     * @param fieldList             导出字段集合对象
     * @return: void
     * @author zhangxiaowei
     **/
    @SneakyThrows
    public static void downloadExcelDynamicHead(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, List<List<String>> headList,
                                                ExcelEnum excelEnum, CustomRowWriteHandler customRowWriteHandler, List<ExcelField> fieldList,HeadStyleStrategy headStyleStrategy) {
        excelEnum = Objects.isNull(excelEnum) ? ExcelEnum.XLSX : excelEnum == ExcelEnum.XLSX ? ExcelEnum.XLSX : ExcelEnum.XLS;
        ExcelTypeEnum excelTypeEnum = ExcelEnum.getExcelTypeEnumByValue(excelEnum);
        /*核验文件名*/
        fileName = checkAndRenameExcelFile(fileName, excelEnum);
        sheetName = StringUtils.isBlank(sheetName) ? "sheet1" : sheetName;

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(getOutputStream(fileName, excelEnum, response));
        if (Objects.nonNull(customRowWriteHandler)) {
            excelWriterBuilder.registerWriteHandler(customRowWriteHandler);
        }
        /*先判断自定义表头是否设置宽度高度,未设置的话使用fieldList内定义的width*/
        if (Objects.nonNull(headStyleStrategy) && (Objects.nonNull(headStyleStrategy.getHeight()) && Objects.nonNull(headStyleStrategy.getWidth()))) {
            excelWriterBuilder.registerWriteHandler(new CustomHeaderCellWriteHandler(headStyleStrategy.getWidth(),headStyleStrategy.getHeight()));
        }else{
            if (!CollectionUtils.isEmpty(fieldList)) {
                excelWriterBuilder.registerWriteHandler(new ExcelAutoColumnWidthStyleStrategy(fieldList));
            }
        }
        //excelWriterBuilder.registerWriteHandler(getHorizontalCellStyleStrategy());
        //设置数字格式的单元格格式
        Map<Integer, FieldDataType> formatMap = JsonParseUtil.getFormatMap(fieldList);
        if (!formatMap.isEmpty()) {
            excelWriterBuilder.registerWriteHandler(new CustomFormatCellWriteHandler(formatMap));
        }
        /*设置样式*/
        HorizontalCellStyleStrategy styleStrategy = Objects.nonNull(headStyleStrategy) ? getHorizontalCellStyleStrategy(headStyleStrategy) :
                getHorizontalCellStyleStrategy();
        excelWriterBuilder.registerWriteHandler(styleStrategy);
        excelWriterBuilder.head(headList).excelType(excelTypeEnum).sheet(sheetName).doWrite(data);
    }


    /**
     * excel数据根据模板文件进行导出
     * 在原有模版基础上添加错误信息head头
     * @param isHasErrHead true:添加错误信息表头 false:不添加
     * @param response
     * @param data
     * @param fieldList 字段映射集合
     * @param excelEnum
     * @param customRowWriteHandler 进度控制器
     * @return: void
     * @author zhangxiaowei
     **/
    @SneakyThrows
    public static void downloadExcelWithTemplate(ExcelConfigProperties excelConfigProperties, String model, HttpServletResponse response,
                                                 List<? extends Object> data, List<ExcelField> fieldList,String exportFileName, ExcelEnum excelEnum,
                                                 CustomRowWriteHandler customRowWriteHandler, boolean isHasErrHead) {
        File exportFile = null;
        File copyFile = null;
        String templateFileName = JsonParseUtil.getTemplateName(model);
        ExcelModel excelModel = JsonParseUtil.getExcelModel(model);
        excelEnum = Objects.isNull(excelEnum) ? ExcelEnum.XLSX : excelEnum == ExcelEnum.XLSX ? ExcelEnum.XLSX : ExcelEnum.XLS;
        try {
            /*核验文件*/
            exportFileName = checkAndRenameExcelFile(exportFileName, excelEnum);
            /*获取模板文件路径*/
            ExcelTypeEnum excelTypeEnum = ExcelEnum.getExcelTypeEnumByValue(excelEnum);
            /*创建导出的excel文件*/
            exportFile = createFile(excelConfigProperties, templateFileName);
            /*创建模板文件的副本*/
            copyFile = copyTemplateFile(excelConfigProperties, templateFileName);
            /*操作副本excel文件，清除初表头外的信息，便于后续withTemplate的操作*/
            deleteRowExcludeHeadRow(copyFile, excelEnum, excelModel.getDataStartRowNumber());
            /*依据复制的模板文件,将导出的数据写入*/
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(exportFile)
                    .withTemplate(new FileInputStream(copyFile));
            /*进度计算*/
            if (Objects.nonNull(customRowWriteHandler)) {
                excelWriterBuilder.registerWriteHandler(customRowWriteHandler);
            }
            //依据json配置的format格式设置excel单元格格式和基础样式
            Map<Integer, FieldDataType> formatMap = JsonParseUtil.getFormatMap(fieldList);
            if (!formatMap.isEmpty()) {
                excelWriterBuilder.registerWriteHandler(new CustomFormatCellWriteHandler(formatMap));
            }
            excelWriterBuilder.inMemory(true).excelType(excelTypeEnum).sheet().doWrite(data);
            /*判断是否要新增错误信息表头*/
            if (isHasErrHead) {
                writeErrTip2Head(exportFile, excelEnum, excelModel.getDataStartRowNumber() - 1);
            }
            /*下载处理好的文件*/
            downloadTemplateFile(exportFile, response, exportFileName,excelEnum);
        } catch (FileNotFoundException exception) {
            throw new RuntimeException(String.format("模板文件%s不存在，请检查", templateFileName));
        } catch (Exception e) {
            throw new RuntimeException("依据模板文件导出异常",e);
        } finally {
            if (copyFile != null) {
                copyFile.delete();
            }
            if (exportFile != null) {
                exportFile.delete();
            }
        }
    }

    /**
     * 创建文件
     *
     * @param excelConfigProperties
     * @param templateFileName
     * @return
     */
    @SneakyThrows
    private static File createFile(ExcelConfigProperties excelConfigProperties, String templateFileName) {
        File templateFile = null;
        /*获取临时生成模板文件的路径*/
        String filePath = FileZipUtil.getExcelPath() + File.separator + excelConfigProperties.getFileTempPath()
                + File.separator + templateFileName;
        /*文件路径不存在,则创建*/
        templateFile = new File(filePath);
        if (!templateFile.getParentFile().exists()) {
            templateFile.getParentFile().mkdirs();
        }
        /*创建文件*/
        if (!templateFile.exists()) {
            templateFile.createNewFile();
        }
        return templateFile;
    }

    /**
     * 生成excel模板文件的副本
     *
     * @param excelConfigProperties
     * @param templateFileName
     * @return
     */
    private static File copyTemplateFile(ExcelConfigProperties excelConfigProperties, String templateFileName) {
        /*获取模板文件输入流*/
        String excelPath = File.separator + excelConfigProperties.getTemplatePath() + File.separator + templateFileName;
        Resource resource = new ClassPathResource(excelPath);
        try {
            InputStream in = resource.getInputStream();
            String copyFileName = templateFileName.substring(0, templateFileName.lastIndexOf(".")) + "_copy" + ExcelEnum.XLSX.getValue();
            File copyFile = createFile(excelConfigProperties, copyFileName);
            OutputStream out = new FileOutputStream(copyFile);
            byte[] arr = new byte[1024];
            int len;
            while ((len = in.read(arr)) != -1) {
                out.write(arr, 0, len);
            }
            in.close();
            out.close();
            return copyFile;
        } catch (Exception e) {
            throw new RuntimeException("copy模板文件发生异常", e);
        }
    }

    /**
     * 删除excel内的数据行(表头除外)
     *
     * @param file
     * @param excelEnum
     */
    private static void deleteRowExcludeHeadRow(File file, ExcelEnum excelEnum, int dataStartRowNumber) {
        try {
            FileInputStream is = new FileInputStream(file);
            Workbook workbook = getWorkbook(is, excelEnum);
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = dataStartRowNumber; i < sheet.getLastRowNum()+1; i++) {
                Row row = sheet.getRow(i);
                sheet.removeRow(row);
            }
            //sheet.shiftRows(dataStartRowNumber, sheet.getLastRowNum(), -1);
            /*追加数据*/
            FileOutputStream os = new FileOutputStream(file);
            workbook.write(os);
            is.close();
            os.close();
        } catch (IOException e) {
            log.error("excel模板导出写入错误提示表头异常");
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取excel文件总行数
     * @param file
     * @return
     */
    public static Integer getTotalRowsByUploadFile(MultipartFile file) {
        InputStream is = null;
        String fileName = file.getOriginalFilename();
        String excelType = fileName.substring(fileName.lastIndexOf("."));
        ExcelEnum excelEnum = ExcelEnum.getExcelEnumByValue(excelType);
        checkAndRenameExcelFile(fileName, excelEnum);
        try {
            is = file.getInputStream();
            Workbook workbook = getWorkbook(is, excelEnum);
            Sheet sheet = workbook.getSheetAt(0);
            is.close();
            return sheet.getLastRowNum() + 1;
        } catch (IOException e) {
            log.error("获取excel文件总行数异常");
            throw new RuntimeException(e);
        }
    }

    /**
     * 生成的临时excel文件，添加错误提示信息表头
     *
     * @param file
     * @param excelEnum
     */
    private static void writeErrTip2Head(File file, ExcelEnum excelEnum, int headStartRowNum) {
        try {
            FileInputStream is = new FileInputStream(file);
            Workbook workbook = getWorkbook(is, excelEnum);
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(headStartRowNum);
            Cell cell = row.createCell(row.getLastCellNum(), CellType.STRING);
            cell.setCellStyle(getErrorheadCellStyle(workbook));
            cell.setCellValue(ExcelCheckConstants.ERROR_HEAD_COL);
            /*追加数据*/
            FileOutputStream os = new FileOutputStream(file);
            workbook.write(os);
            is.close();
            os.close();
        } catch (IOException e) {
            log.error("excel模板导出写入错误提示表头异常");
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取临时文件Workbook
     *
     * @param is
     * @param excelEnum
     * @return
     * @throws IOException
     */
    private static Workbook getWorkbook(InputStream is, ExcelEnum excelEnum) throws IOException {
        Workbook wb = null;
        if (!is.markSupported()) {
            is = new PushbackInputStream(is, 8);
        }
        if (excelEnum.equals(ExcelEnum.XLS)) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        return wb;

    }

    /**
     * 获取错误提示列表头样式
     *
     * @param workbook
     * @return
     */
    private static CellStyle getErrorheadCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        Font headWriteFont = workbook.createFont();
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteFont.setFontName("Calibri");
        headWriteFont.setBold(false);
        headWriteFont.setColor(IndexedColors.RED.getIndex());
        cellStyle.setFont(headWriteFont);
        return cellStyle;
    }


    /**
     * 下载根据模板导出生成的临时文件
     *
     * @param templateFile
     * @param response
     * @param exportFileName
     */
    private static void downloadTemplateFile(File templateFile, HttpServletResponse response, String exportFileName,ExcelEnum excelEnum) {
        try {
            /*读取临时模板文件流对象*/
            InputStream bis = new FileInputStream(templateFile);
            response.setContentType(excelEnum.getContentType());
            response.setCharacterEncoding("utf8");
            exportFileName = URLEncoder.encode(exportFileName, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
            int len = 0;
            byte[] arr = new byte[1024];
            while ((len = bis.read(arr)) != -1) {
                out.write(arr,0,len);
                out.flush();
            }
            out.close();
            bis.close();
        } catch (Exception e) {
            log.error("文件下载失败", e);
            throw new RuntimeException(e);
        }

    }

    /**
     * 校验文件名，如若不一致则重命名
     * @param fileName
     * @param excelEnum
     * @return
     */
    private static String checkAndRenameExcelFile(String fileName,ExcelEnum excelEnum) {
        if (fileName.indexOf(".") != fileName.lastIndexOf(".")) {
            throw new RuntimeException("非法的excel文件名!");
        }
        if (!fileName.endsWith(ExcelEnum.XLSX.getValue()) && !fileName.endsWith(ExcelEnum.XLS.getValue())) {
            throw new RuntimeException("非法的excel文件名!");
        }
        /*如果传入的文件名与声明的ExcelEnum不一致，以ExcelEnum为准重命名文件*/
        if (!fileName.endsWith(excelEnum.getValue())) {
            fileName = fileName.substring(0, fileName.lastIndexOf(".")) + excelEnum.getValue();
        }
        return fileName;
    }

    /**
     * 在指定路径下生成file对象,并返回当前file对象的路径
     * 传入CustomCellWriteHandler对象，表示当前有进度计算
     * @param excelConfigProperties
     * @param data
     * @param fileName
     * @param sheetName
     * @param headList
     * @param excelEnum
     * @param customRowWriteHandler 进度控制器
     * @param fieldList             导出字段集合对象
     * @return: java.lang.String
     * @author zhangxiaowei
     **/
    @SneakyThrows
    public static String writeExcelDynamicHead(ExcelConfigProperties excelConfigProperties, List<? extends Object> data, String fileName, String sheetName, List<List<String>> headList, ExcelEnum excelEnum, CustomRowWriteHandler customRowWriteHandler, List<ExcelField> fieldList) {
        excelEnum = Objects.isNull(excelEnum) ? ExcelEnum.XLSX : excelEnum == ExcelEnum.XLSX ? ExcelEnum.XLSX : ExcelEnum.XLS;
        /*1.核验文件名*/
        fileName = checkAndRenameExcelFile(fileName, excelEnum);
        /*2.创建文件*/
        String filePath = FileZipUtil.getExcelPath() + File.separator + excelConfigProperties.getFileTempPath()
                + File.separator + fileName;
        File file = createFile(excelConfigProperties, fileName);
        sheetName = StringUtils.isBlank(sheetName) ? "sheet1" : sheetName;
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(file);
        if (Objects.nonNull(customRowWriteHandler)) {
            excelWriterBuilder.registerWriteHandler(customRowWriteHandler);
        }
        if (!CollectionUtils.isEmpty(fieldList)) {
            excelWriterBuilder.registerWriteHandler(new ExcelAutoColumnWidthStyleStrategy(fieldList));
        }
        //设置导出数据的单元格格式
        Map<Integer, FieldDataType> formatMap = JsonParseUtil.getFormatMap(fieldList);
        if (!formatMap.isEmpty()) {
            excelWriterBuilder.registerWriteHandler(new CustomFormatCellWriteHandler(formatMap));
        }
        excelWriterBuilder.head(headList)
                .excelType(ExcelEnum.getExcelTypeEnumByValue(excelEnum)).sheet(sheetName).registerWriteHandler(getHorizontalCellStyleStrategy()).doWrite(data);
        return filePath;
    }

    /**
     * 获取HorizontalCellStyleStrategy
     * 默认样式
     *
     * @return
     */
    private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }


    /**
     * 自定义表头样式
     * @param headStyleStrategy
     * @return
     */
    private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy(HeadStyleStrategy headStyleStrategy) {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(headStyleStrategy.getHorizontalAlignment());
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(headStyleStrategy.getIndexedColors().getIndex());
        /*设置字体*/
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints(headStyleStrategy.getFontSize());
        headWriteCellStyle.setWriteFont(headWriteFont);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

    /**
     * 获取excel输出流
     *
     * @param fileName
     * @param excelEnum
     * @param response
     * @return
     * @throws Exception
     */
    private static OutputStream getOutputStream(String fileName, ExcelEnum excelEnum, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType(excelEnum.getContentType());
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        return response.getOutputStream();
    }

}

